﻿namespace Common
{
    /// <summary>
    /// Summary description for Constant
    /// </summary>
    public class Constant
    {
        //Parameter area
        public const string ParameterId = "@Id";
        public const string ParameterTitle = "@Title";
        public const string ParameterContent = "@Content";
        public const string ParameterCategory = "@Category";
        public const string ParameterBrief = "@Brief";
        public const string ParameterIsActive = "@IsActive";
        public const string ParameterKeyWord = "@KeyWord";
        public const string ParameterFrom = "@From";
        public const string ParameterTo = "@To";
        public const string ParameterTop = "@Top";
        public const string ParameterArticle = "@Article";
        public const string ParameterPostedPerson = "@PostedPerson";
        public const string ParameterPostedDate = "@PostedDate";
        public const string ParameterMessage = "@Message";

        //Comment area
        public const string GetCountAllCommentByArticleId = @"select count(*) from Comment 
                                                            where Article = @Article";

        public const string GetAllCommentByArticleId = @"select * from  (select row_number() over(order by PostedDate desc) as
                                                       rownum, * from Comment 
                                                       where Article = @Article) as Result
                                                       where rownum >= @From and rownum <= @To";

        public const string CreateComment = @"insert into Comment (Id, Article, PostedPerson, [Message], PostedDate)
                                            values (@Id, @Article, @PostedPerson, @Message, @PostedDate)";

        public const string DeleteComment = @"delete Comment where Id = @Id";
                                            

        //Category area
        public const string GetAllCategory = "select * from Category";

        public const string CheckCategory = @"select top(1) Id from Category
                                            where Id = @Id";
        //Article area
        public const string CheckArticle = @"select top(1) Id from Article
                                           where Id = @Id";

        public const string UpdateArticleTitle = @"update Article 
                                                 set Title = @Title 
                                                 where Id = @Id";

        public const string UpdateArticleContent = @"update Article 
                                                   set [Content] = @Content 
                                                   where Id = @Id";

        public const string UpdateArticleCategory = @"update Article 
                                                    set [Category] = @Category 
                                                    where Id = @Id";

        public const string UpdateArticleBrief = @"update Article 
                                                 set Brief = @Brief 
                                                 where Id = @Id";

        public const string UpdateArticleIsActive = @"update Article 
                                                    set IsActive = @IsActive 
                                                    where Id = @Id";

        public const string GetArticleById = @"select * from Article
                                             where Id = @Id
                                             and IsActive = 1";


        public const string GetCountArticlesByKeyWord = @"select count(*) from Article 
                                                        where ([User] like @KeyWord
                                                        or [Content] like @KeyWord
                                                        or Title like @KeyWord)
                                                        and IsActive = 1";

        public const string GetArticlesByKeyword = @"select * from  (select row_number() over(order by PostedDate) as
                                                   rownum, * from Article 
                                                   where ([User] like @KeyWord
                                                   or [Content] like @KeyWord
                                                   or Title like @KeyWord)
                                                   and IsActive = 1) as Result
                                                   where rownum >= @From and rownum <= @To";

        public const string GetTopArticlesByPostedDate = @"select top(@Top)* from Article
                                                         where IsActive = 1
                                                         order by PostedDate desc";
    }
}